package net.pws.oos.biz.query.impl;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.pws.common.beanutils.BeanUtils;
import net.pws.common.util.DateUtils;
import net.pws.common.persistence.IPage;
import net.pws.common.persistence.Page;
import net.pws.common.persistence.QueryRepository;
import net.pws.oos.biz.EntityUtils;
import net.pws.oos.biz.model.Organization;
import net.pws.oos.biz.model.Role;
import net.pws.oos.biz.model.User;
import net.pws.oos.biz.query.SystemManageFinder;
import net.pws.oos.biz.query.parameter.OperateLogQueryParameter;
import net.pws.oos.biz.query.parameter.OrganizationQueryParameter;
import net.pws.oos.biz.query.parameter.RoleQueryParameter;
import net.pws.oos.biz.query.parameter.UserQueryParameter;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

@Service
@SuppressWarnings({ "rawtypes", "unchecked" })
public class DefaultSystemManageFinder extends AbstractFinder implements
                                                             SystemManageFinder {
    
    public User getUser(String id) {
        return (User) entityManager.findFirst("select u from User u left join fetch u.organization where u.id = ?",
                                              new Object[] { id });
    }
    
    public User getUserMap(String id) {
        User user = null;
        Map map = (Map) entityManager.findFirst("select new map(u.id as id,u.name as name,u.account as account,u.mobile as mobile,o.id as organId,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,u.description as description) from User u left join u.organization o where u.id = ?",
                                                new Object[] { id });
        if (map != null) {
            user = new User();
            BeanUtils.copyProperties(user, map);
            String organId = (String) map.get("organId");
            if (StringUtils.isNotEmpty(organId)) {
                Organization org = entityManager.find(Organization.class,
                                                      organId);
                user.setOrganization(org);
            }
        }
        return user;
    }
    
    public Role getRole(String id) {
        return entityManager.find(Role.class, id);
    }
    
    // user
    public int countUser(UserQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(u.id) from User u left join u.organization where u.sourceSystem is not null and u.deleted = false and u.privileged = false ");
        List<Object> params = new ArrayList<Object>();
        buildHqlQueryRequest(queryParameter, hql, null, params);
        Number count = (Number) entityManager.findUnique(hql.toString(),
                                                         params.toArray());
        return count.intValue();
        // return ((Number)
        // entityManager.findUnique("select count(u.id) from User u where u.sourceSystem is not null and u.deleted = false ",
        // new Object[] {})).intValue();
    }
    
    public List<User> getUserList(UserQueryParameter queryParameter,
                                  int start,
                                  int limit) {
        
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return (List<User>) ((IPage) entityManager.findPage("select count(u.id) from User u where u.sourceSystem is not null and  u.deleted = false and u.privileged = false and (u.name like ? or u.account like ?)",
                                                                "select u from User u where u.sourceSystem is not null and  u.deleted = false and u.privileged = false and (u.name like ? or u.account like ?)",
                                                                new Object[] { "%" + queryParameter.getName()
                                                                               + "%",
                                                                              "%"     + queryParameter.getName()
                                                                                      + "%" },
                                                                start,
                                                                limit)).getData();
        }
        return (List<User>) ((IPage) entityManager.findPage("select count(u.id) from User u where u.sourceSystem is not null and  u.deleted = false and u.privileged = false ",
                                                            "select u from User u left join u.organization where u.sourceSystem is not null and u.deleted = false and u.privileged = false ",
                                                            new HashMap(),
                                                            start,
                                                            limit)).getData();
    }
    
    public int countUserWithRoles(UserQueryParameter queryParameter) {
        List<Object> params = new ArrayList<Object>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(*) as recordCount from (");
        sql.append(QueryRepository.getInstance().get("sql.listUser"));
        buildSqlQueryRequest(sql, params, queryParameter);
        sql.append(" group by u.id) a ");
        Map map = (Map) entityManager.nativeFindFirst(sql.toString(),
                                                      params.toArray());
        return ((Number) map.get("recordCount")).intValue();
    }
    
    public List<Map> listUserWithRoles(UserQueryParameter queryParameter) {
        StringBuilder sql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("sql.listUser"));
        List<Object> params = new ArrayList<Object>();
        
        buildSqlQueryRequest(sql, params, queryParameter);
        sql.append(" group by u.id order by u.name ");
        
        Page<Map> page = (Page<Map>) entityManager.nativeFindPage(sql.toString(),
                                                                  params.toArray(),
                                                                  queryParameter.getStart(),
                                                                  queryParameter.getLimit());
        return page.getData();
    }
    
    private void buildSqlQueryRequest(StringBuilder sql,
                                      List<Object> params,
                                      UserQueryParameter queryParameter) {
        if (StringUtils.isNotBlank(queryParameter.getName())) {
            sql.append(" and (u.name like ? or u.account like ? or u.mobile like ?) ");
            String like = "%" + queryParameter.getName() + "%";
            params.add(like);
            params.add(like);
            params.add(like);
        }
        if (StringUtils.isNotBlank(queryParameter.getOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getOrganId());
            if (org != null) {
                sql.append(" and o.inner_code like ? ");
                params.add(org.getInnerCode() + "%");
            }
        }
        if (StringUtils.isNotBlank(queryParameter.getRoleId())) {
            sql.append(" and r.id=? ");
            params.add(queryParameter.getRoleId());
        }
    }
    
    public List<User> getUserListByOrgan(String organId) {
        String hql = "from User u where u.organization.id=? and u.deleted = false and u.privileged = false ";
        return entityManager.findList(hql, new Object[] { organId });
    }
    
    public List<User> getUserListByOrganTree(String organId) {
        Organization organ = entityManager.find(Organization.class, organId);
        if (organ == null) {
            return Collections.EMPTY_LIST;
        }
        
        String likeCode = organ.getInnerCode() + "%";
        String hql = "from User u where u.organization.innerCode like ? ";
        return entityManager.findList(hql, new Object[] { likeCode });
    }
    
    public List<User> getUserList(String ids) {
        if (StringUtils.isBlank(ids)) {
            return Collections.EMPTY_LIST;
        }
        StringBuilder hql = new StringBuilder("from User u where u.id in (");
        String[] idArray = ids.split(",");
        for (int i = 0; i < idArray.length; i++) {
            if (i > 0) {
                hql.append(",");
            }
            hql.append("'").append(idArray[i]).append("'");
        }
        hql.append(")");
        return entityManager.findList(hql.toString(), new Object[] {});
    }
    
    public List<Map> getUserMapList(UserQueryParameter queryParameter,
                                    int start,
                                    int limit) {
        StringBuilder hql1 = new StringBuilder("select count(u.id) from User u left join u.organization where u.sourceSystem is not null and  u.deleted = false and u.privileged = false ");
        StringBuilder hql2 = new StringBuilder("select new map(u.id as id,u.name as name,u.account as account,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is not null and  u.deleted = false and u.privileged = false ");
        List<Object> params = new ArrayList<Object>();
        buildHqlQueryRequest(queryParameter, hql1, hql2, params);
        
        return (List<Map>) ((IPage) entityManager.findPage(hql1.toString(),
                                                           hql2.toString(),
                                                           params.toArray(),
                                                           start,
                                                           limit)).getData();
        //
        // if (StringUtils.isNotEmpty(queryParameter.getName())) {
        // return (List<Map>) ((IPage)
        // entityManager.findPage("select count(u.id) from User u where u.sourceSystem is not null and  u.deleted = false and (u.name like ? or u.account like ?)",
        // "select new map(u.id as id,u.name as name,u.account as account,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is not null and  u.deleted = false and (u.name like ? or u.account like ?)",
        // new Object[] { "%" + queryParameter.getName()
        // + "%",
        // "%" + queryParameter.getName()
        // + "%" },
        // start,
        // limit)).getData();
        // }
        // return (List<Map>) ((IPage)
        // entityManager.findPage("select count(u.id) from User u where u.sourceSystem is not null and  u.deleted = false ",
        // "select new map(u.id as id,u.name as name,u.account as account,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is not null and  u.deleted = false ",
        // new HashMap(),
        // start,
        // limit)).getData();
    }
    
    // user
    public int countOurUser(UserQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder("select count(u.id) from User u left join u.organization where u.sourceSystem is null and u.deleted = false and u.privileged = false ");
        List<Object> params = new ArrayList<Object>();
        buildHqlQueryRequest(queryParameter, hql, null, params);
        Number count = (Number) entityManager.findUnique(hql.toString(),
                                                         params.toArray());
        return count.intValue();
        
        // if (StringUtils.isNotEmpty(queryParameter.getName())) {
        // return ((Number)
        // entityManager.findUnique("select count(u.id) from User u where u.sourceSystem is null and  u.deleted = false and (u.name like ? or u.account like ?)",
        // new Object[] { "%" + queryParameter.getName()
        // + "%",
        // "%" + queryParameter.getName()
        // + "%" })).intValue();
        // }
        // return ((Number)
        // entityManager.findUnique("select count(u.id) from User u where u.sourceSystem is null and  u.deleted = false ",
        // new Object[] {})).intValue();
    }
    
    public List<User> getOurUserList(UserQueryParameter queryParameter,
                                     int start,
                                     int limit) {
        
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return (List<User>) ((IPage) entityManager.findPage("select count(u.id) from User u where u.sourceSystem is null and u.deleted = false and u.privileged = false and (u.name like ? or u.account like ?)",
                                                                "select u from User u where u.sourceSystem is null and u.deleted = false and (u.name like ? or u.account like ?)",
                                                                new Object[] { "%" + queryParameter.getName()
                                                                               + "%",
                                                                              "%"     + queryParameter.getName()
                                                                                      + "%" },
                                                                start,
                                                                limit)).getData();
        }
        return (List<User>) ((IPage) entityManager.findPage("select count(u.id) from User u where u.sourceSystem is null and u.deleted = false and u.privileged = false ",
                                                            "select u from User u left join u.organization  where u.sourceSystem is null and  u.deleted = false ",
                                                            new HashMap(),
                                                            start,
                                                            limit)).getData();
    }
    
    public List<Map> getOurUserMapList(UserQueryParameter queryParameter,
                                       int start,
                                       int limit) {
        StringBuilder hql1 = new StringBuilder("select count(u.id) from User u left join u.organization where u.sourceSystem is null and  u.deleted = false and u.privileged = false ");
        StringBuilder hql2 = new StringBuilder("select new map(u.id as id,u.name as name,u.account as account,u.mobile as mobile,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is null and  u.deleted = false and u.privileged = false ");
        List<Object> params = new ArrayList<Object>();
        buildHqlQueryRequest(queryParameter, hql1, hql2, params);
        
        return (List<Map>) ((IPage) entityManager.findPage(hql1.toString(),
                                                           hql2.toString(),
                                                           params.toArray(),
                                                           start,
                                                           limit)).getData();
        
        // if (StringUtils.isNotEmpty(queryParameter.getName())) {
        // return (List<Map>) ((IPage)
        // entityManager.findPage("select count(u.id) from User u where u.sourceSystem is null and u.deleted = false and (u.name like ? or u.account like ?)",
        // "select new map(u.id as id,u.name as name,u.account as account,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is null and u.deleted = false and (u.name like ? or u.account like ?)",
        // new Object[] { "%" + queryParameter.getName()
        // + "%",
        // "%" + queryParameter.getName()
        // + "%" },
        // start,
        // limit)).getData();
        // }
        // return (List<Map>) ((IPage)
        // entityManager.findPage("select count(u.id) from User u where u.sourceSystem is null and u.deleted = false ",
        // "select new map(u.id as id,u.name as name,u.account as account,u.enabled as enabled,u.webAccessAllowed as webAccessAllowed,u.mobileAccessAllowed as mobileAccessAllowed,o.name as organName) from User u left join u.organization o where u.sourceSystem is null and  u.deleted = false ",
        // new HashMap(),
        // start,
        // limit)).getData();
    }
    
    private void buildHqlQueryRequest(UserQueryParameter queryParameter,
                                      StringBuilder hql1,
                                      StringBuilder hql2,
                                      List<Object> params) {
        if (queryParameter == null) {
            return;
        }
        if (hql2 == null) {
            hql2 = new StringBuilder();
        }
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            if (queryParameter.getName()
                              .startsWith(UserQueryParameter.VAGUE_ACCOUNT)) {
                hql1.append(" and u.account like ? ");
                hql2.append(" and u.account like ? ");
                String like = "%" + queryParameter.getName()
                                                  .substring(UserQueryParameter.VAGUE_ACCOUNT.length())
                                                  .trim()
                              + "%";
                params.add(like);
            }
            if (queryParameter.getName()
                              .startsWith(UserQueryParameter.VAGUE_MOBILE)) {
                hql1.append(" and u.mobile like ? ");
                hql2.append(" and u.mobile like ? ");
                String like = "%" + queryParameter.getName()
                                                  .substring(UserQueryParameter.VAGUE_MOBILE.length())
                                                  .trim()
                              + "%";
                params.add(like);
            }
            else if (queryParameter.getName()
                                   .startsWith(UserQueryParameter.VAGUE_ORGAN)) {
                hql1.append(" and u.organization.name like ? ");
                hql2.append(" and u.organization.name like ? ");
                String like = "%" + queryParameter.getName()
                                                  .substring(UserQueryParameter.VAGUE_ORGAN.length())
                                                  .trim()
                              + "%";
                params.add(like);
            }
            else {
                hql1.append(" and u.name like ? ");
                hql2.append(" and u.name like ? ");
                String like = "%" + queryParameter.getName() + "%";
                params.add(like);
            }
        }
        if (StringUtils.isNotEmpty(queryParameter.getOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getOrganId());
            if (org != null) {
                hql1.append(" and u.organization.innerCode like ?");
                hql2.append(" and u.organization.innerCode like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
        if (StringUtils.isNotEmpty(queryParameter.getOrganUserLike())) {
            String like = "%" + queryParameter.getOrganUserLike() + "%";
            hql1.append(" and (u.organization.name like ? or u.name like  ? or u.mobile like ?) ");
            hql2.append(" and (u.organization.name like ? or u.name like  ? or u.mobile like ?) ");
            params.add(like);
            params.add(like);
            params.add(like);
        }
    }
    
    // user bind
    private static final String countBindedUserSql = "select count(u.id) from s_user u where u.has_deleted = 0 and u.id in (select user_id from s_user_role ur where ur.role_id  = ?)";
    
    public int countBindedUser(String roleId) {
        return ((Number) ((Map) entityManager.nativeFindUnique(countBindedUserSql,
                                                               new Object[] { roleId })).values()
                                                                                        .iterator()
                                                                                        .next()).intValue();
    }
    
    private static final String getBindedUserListSql = "select u.* from s_user u where u.has_deleted = 0 and u.id in (select user_id from s_user_role ur where ur.role_id  = ?)";
    
    public List<Map> getBindedUserList(String roleId, int start, int limit) {
        int startOfPage = Page.getStartOfPage(start, limit);
        if (startOfPage == 0) {
            return (List<Map>) entityManager.nativeFindList(getBindedUserListSql + "limit ?",
                                                            new Object[] { roleId,
                                                                          limit });
        }
        else {
            return (List<Map>) entityManager.nativeFindList(getBindedUserListSql + "limit ?,?",
                                                            new Object[] { roleId,
                                                                          startOfPage,
                                                                          startOfPage + limit });
        }
    }
    
    private static String countUnbindedUserSql = "select count(u.id) from s_user u where u.has_deleted = 0 and u.id not in (select user_id from s_user_role ur where ur.role_id = ? ) ";
    
    public int countUnbindedUser(RoleQueryParameter queryParameter) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return ((Number) ((Map) entityManager.nativeFindUnique(countUnbindedUserSql + " and (u.name like ? or u.account like ?)",
                                                                   new Object[] { queryParameter.getRoleId(),
                                                                                 "%"     + queryParameter.getName()
                                                                                         + "%",
                                                                                 "%"     + queryParameter.getName()
                                                                                         + "%" })).values()
                                                                                                  .iterator()
                                                                                                  .next()).intValue();
        }
        return ((Number) ((Map) entityManager.nativeFindUnique(countUnbindedUserSql,
                                                               new Object[] { queryParameter.getRoleId() })).values()
                                                                                                            .iterator()
                                                                                                            .next()).intValue();
    }
    
    private static String getUnbindedUserListSql = "select u.* from s_user u where u.has_deleted = 0 and u.id not in (select user_id from s_user_role ur where ur.role_id  = ? ) ";
    
    public List<Map> getUnbindedUserList(RoleQueryParameter queryParameter,
                                         int start,
                                         int limit) {
        List parameter = new ArrayList();
        parameter.add(queryParameter.getRoleId());
        
        StringBuffer sb = new StringBuffer(getUnbindedUserListSql);
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            sb.append("and (u.name like ? or u.account like ?)");
            parameter.add("%" + queryParameter.getName() + "%");
            parameter.add("%" + queryParameter.getName() + "%");
        }
        int startOfPage = Page.getStartOfPage(start, limit);
        if (startOfPage == 0) {
            sb.append("limit ?");
            parameter.add(limit);
        }
        else {
            sb.append("limit ?,?");
            parameter.add(startOfPage);
            parameter.add(startOfPage + limit);
        }
        
        return (List<Map>) entityManager.nativeFindList(sb.toString(),
                                                        parameter.toArray());
    }
    
    // role bind
    private static String countBindedRoleSql = "select count(r.id) from s_role r where r.id in (select ur.role_id from s_user_role ur where ur.user_id = ?)";
    
    public int countBindedRole(String userId) {
        return ((Number) ((Map) entityManager.nativeFindUnique(countBindedRoleSql,
                                                               new Object[] { userId })).values()
                                                                                        .iterator()
                                                                                        .next()).intValue();
    }
    
    private static String getBindedRoleListSql = "select r.* from s_role r where r.id in (select ur.role_id from s_user_role ur where ur.user_id = ?) order by r.id ";
    
    public List<Map> getBindedRoleList(String userId, int start, int limit) {
        int startOfPage = Page.getStartOfPage(start, limit);
        if (startOfPage == 0) {
            return (List<Map>) entityManager.nativeFindList(getBindedRoleListSql + "limit ?",
                                                            new Object[] { userId,
                                                                          limit });
        }
        else {
            return (List<Map>) entityManager.nativeFindList(getBindedRoleListSql + "limit ?,?",
                                                            new Object[] { userId,
                                                                          startOfPage,
                                                                          startOfPage + limit });
        }
    }
    
    private static String countUnbindedRoleSql = "select count(r.id) from s_role r where r.id not in (select ur.role_id from s_user_role ur where ur.user_id = ?)";
    
    public int countUnbindedRole(UserQueryParameter queryParameter) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return ((Number) ((Map) entityManager.nativeFindUnique(countUnbindedRoleSql + " (r.name like ? or r.code like ?)",
                                                                   new Object[] { queryParameter.getUserId(),
                                                                                 "%"     + queryParameter.getName()
                                                                                         + "%",
                                                                                 "%"     + queryParameter.getName()
                                                                                         + "%" })).values()
                                                                                                  .iterator()
                                                                                                  .next()).intValue();
        }
        return ((Number) ((Map) entityManager.nativeFindUnique(countUnbindedRoleSql,
                                                               new Object[] { queryParameter.getUserId() })).values()
                                                                                                            .iterator()
                                                                                                            .next()).intValue();
    }
    
    private static String getUnbindedRoleListSql = "select * from s_role r where r.id not in (select ur.role_id from s_user_role ur where ur.user_id = ?)";
    
    public List<Map> getUnbindedRoleList(UserQueryParameter queryParameter,
                                         int start,
                                         int limit) {
        List parameter = new ArrayList();
        parameter.add(queryParameter.getUserId());
        
        StringBuffer sb = new StringBuffer(getUnbindedRoleListSql);
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            sb.append("and (r.name like ? or r.account like ?)");
            parameter.add("%" + queryParameter.getName() + "%");
            parameter.add("%" + queryParameter.getName() + "%");
        }
        sb.append(" order by id ");
        
        int startOfPage = Page.getStartOfPage(start, limit);
        if (startOfPage == 0) {
            sb.append("limit ?");
            parameter.add(limit);
        }
        else {
            sb.append("limit ?,?");
            parameter.add(startOfPage);
            parameter.add(startOfPage + limit);
        }
        
        return (List<Map>) entityManager.nativeFindList(sb.toString(),
                                                        parameter.toArray());
    }
    
    // role
    public int countRole(RoleQueryParameter queryParameter) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return ((Number) entityManager.findUnique("select count(u.id) from Role u where u.name like ? or u.code like ?",
                                                      new Object[] { "%" + queryParameter.getName()
                                                                     + "%",
                                                                    "%"     + queryParameter.getName()
                                                                            + "%" })).intValue();
        }
        
        return ((Number) entityManager.findUnique("select count(r.id) from Role r",
                                                  new HashMap())).intValue();
    }
    
    public List<Role> getRoleList(RoleQueryParameter queryParameter,
                                  int start,
                                  int limit) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return (List<Role>) ((IPage) entityManager.findPage("select count(r.id) from Role r where r.name like ? or r.code like ?",
                                                                "select r from Role r where r.name like ? or r.code like ? order by id ",
                                                                new Object[] { "%" + queryParameter.getName()
                                                                               + "%",
                                                                              "%"     + queryParameter.getName()
                                                                                      + "%" },
                                                                start,
                                                                limit)).getData();
        }
        
        return (List<Role>) ((IPage) entityManager.findPage("select count(r.id) from Role r",
                                                            "from Role r order by id",
                                                            new Object[] {},
                                                            0,
                                                            20)).getData();
    }
    
    // organization
    public int countOrganization(OrganizationQueryParameter queryParameter) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return ((Number) entityManager.findUnique("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false and (o.name like ? or o.shortName like ?)",
                                                      new Object[] { "%" + queryParameter.getName()
                                                                     + "%",
                                                                    "%"     + queryParameter.getName()
                                                                            + "%" })).intValue();
        }
        
        return ((Number) entityManager.findUnique("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false ",
                                                  new HashMap())).intValue();
    }
    
    public List<Organization> getOrganizationList(OrganizationQueryParameter queryParameter,
                                                  int start,
                                                  int limit) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return (List<Organization>) ((IPage) entityManager.findPage("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false and ( o.name like ? or o.shortName like ?)",
                                                                        "select o from Organization o where o.sourceSystem is not null and o.deleted = false and (o.name like ? or o.shortName like ?)  order by o.depth,o.name",
                                                                        new Object[] { "%" + queryParameter.getName()
                                                                                       + "%",
                                                                                      "%"     + queryParameter.getName()
                                                                                              + "%" },
                                                                        start,
                                                                        limit)).getData();
        }
        
        return (List<Organization>) ((IPage) entityManager.findPage("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false ",
                                                                    "select o.name from Organization o left join o.parent where o.sourceSystem is not null and o.deleted = false order by o.depth,o.name",
                                                                    new Object[] {},
                                                                    start,
                                                                    limit)).getData();
    }
    
    public List<Map> getOrganizationMapList(OrganizationQueryParameter queryParameter,
                                            int start,
                                            int limit) {
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            return (List<Map>) ((IPage) entityManager.findPage("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false and ( o.name like ? or o.shortName like ?)",
                                                               "select new map(o.id as id,o.name as name,o.shortName as shortName,o.code as code,p.name as parentName) from Organization o left join o.parent p where o.sourceSystem is not null and o.deleted = false and (o.name like ? or o.shortName like ?)  order by o.depth,o.name",
                                                               new Object[] { "%" + queryParameter.getName()
                                                                              + "%",
                                                                             "%"     + queryParameter.getName()
                                                                                     + "%" },
                                                               start,
                                                               limit)).getData();
        }
        
        return (List<Map>) ((IPage) entityManager.findPage("select count(o.id) from Organization o where o.sourceSystem is not null and o.deleted = false ",
                                                           "select new map(o.id as id,o.name as name,o.shortName as shortName,o.code as code,p.name as parentName) from Organization o left join o.parent p where o.sourceSystem is not null and o.deleted = false order by o.depth,o.name",
                                                           new Object[] {},
                                                           start,
                                                           limit)).getData();
    }
    
    public List<Organization> getOrganizationChildren(String id) {
        if (StringUtils.isEmpty(id)) {
            return (List<Organization>) entityManager.findList("from Organization o where o.deleted = false and o.depth = 0 order by o.name",
                                                               new Object[] {});
        }
        return (List<Organization>) entityManager.findList("from Organization o where o.deleted = false and o.parent.id = ? order by o.depth,o.name",
                                                           new Object[] { id });
    }
    
    public List<Organization> getOrganizationChildren(String id,
                                                      String excludeId) {
        if (StringUtils.isEmpty(id)) {
            return (List<Organization>) entityManager.findList("from Organization o where o.deleted = false and o.depth = 0 and o.id !=? order by o.name",
                                                               new Object[] { excludeId });
        }
        return (List<Organization>) entityManager.findList("from Organization o where o.deleted = false and o.parent.id = ? and o.id !=? and o.parent.id !=? order by o.depth,o.name",
                                                           new Object[] { id,
                                                                         excludeId,
                                                                         excludeId });
    }
    
    private static String countOrganHql = "select count(o.id) from Organization o where o.sourceSystem is null and o.deleted = false ";
    
    private static String listOrganHql = "select o from Organization o where o.sourceSystem is null and o.deleted = false ";
    
    private static String listOrganMapHql = "select new map(o.id as id,o.name as name,o.shortName as shortName,o.code as code,p.name as parentName) from Organization o left join o.parent p where o.sourceSystem is null and o.deleted = false ";
    
    private void buildOrganQueryRequest(OrganizationQueryParameter queryParameter,
                                        StringBuilder hql1,
                                        StringBuilder hql2,
                                        List<Object> params) {
        if (queryParameter == null || params == null) {
            return;
        }
        if (hql2 == null) {
            hql2 = new StringBuilder();
        }
        
        if (StringUtils.isNotEmpty(queryParameter.getName())) {
            hql1.append(" and (o.name like ? or o.shortName like ?) ");
            hql2.append(" and (o.name like ? or o.shortName like ?) ");
            params.add("%" + queryParameter.getName() + "%");
            params.add("%" + queryParameter.getName() + "%");
        }
        
        if (StringUtils.isNotEmpty(queryParameter.getAncestorId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getAncestorId());
            if (org != null) {
                hql1.append(" and o.innerCode like ?");
                hql2.append(" and o.innerCode like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
    }
    
    public int countOurOrganization(OrganizationQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder(countOrganHql);
        List<Object> params = new ArrayList<Object>();
        buildOrganQueryRequest(queryParameter, hql, null, params);
        
        Number count = (Number) entityManager.findUnique(hql.toString(),
                                                         params.toArray());
        return count.intValue();
    }
    
    public List<Organization> getOurOrganizationList(OrganizationQueryParameter queryParameter,
                                                     int start,
                                                     int limit) {
        StringBuilder hql = new StringBuilder(listOrganHql);
        StringBuilder hql4Count = new StringBuilder(countOrganHql);
        List<Object> params = new ArrayList<Object>();
        buildOrganQueryRequest(queryParameter, hql, hql4Count, params);
        hql.append(" order by o.depth,o.name ");
        
        IPage page = entityManager.findPage(hql4Count.toString(),
                                            hql.toString(),
                                            params.toArray(),
                                            start,
                                            limit);
        return page.getData();
    }
    
    public List<Map> getOurOrganizationMapList(OrganizationQueryParameter queryParameter,
                                               int start,
                                               int limit) {
        StringBuilder hql = new StringBuilder(listOrganMapHql);
        StringBuilder hql4Count = new StringBuilder(countOrganHql);
        List<Object> params = new ArrayList<Object>();
        buildOrganQueryRequest(queryParameter, hql, hql4Count, params);
        hql.append(" order by o.depth,o.name ");
        
        IPage page = entityManager.findPage(hql4Count.toString(),
                                            hql.toString(),
                                            params.toArray(),
                                            start,
                                            limit);
        return page.getData();
    }
    
    public Organization getOrganization(String id) {
        return (Organization) entityManager.findFirst("select o from Organization o left join o.parent where o.deleted = false and o.id = ?",
                                                      new Object[] { id });
    }
    
    public Organization getOrganizationMap(String id) {
        Organization organization = null;
        Map map = (Map) entityManager.findFirst("select new map(o.id as id,o.name as name,o.shortName as shortName,o.code as code,p.id as parentId,o.districtName as districtName,o.districtCode as districtCode,o.longitude as longitude,o.latitude as latitude,o.description as description)from Organization o left join o.parent p where o.deleted = false and o.id = ?",
                                                new Object[] { id });
        if (map != null) {
            organization = new Organization();
            BeanUtils.copyProperties(organization, map);
            String parentId = (String) map.get("parentId");
            if (StringUtils.isNotEmpty(parentId)) {
                Organization parent = entityManager.find(Organization.class,
                                                         parentId);
                organization.setParent(parent);
            }
        }
        
        return organization;
    }
    
    @Override
    public Organization getDepthOrganization(String id, int depth) {
        Organization source = entityManager.find(Organization.class, id);
        if (source == null) {
            return null;
        }
        
        if (source.getDepth() < depth) {
            return null;
        }
        
        if (source.getDepth() == depth) {
            return source;
        }
        
        String innerCode = EntityUtils.getValidInnerCode(source.getInnerCode(),
                                                         depth);
        return getOrganizationByInnerCode(innerCode);
    }
    
    private Organization getOrganizationByInnerCode(String innerCode) {
        return (Organization) entityManager.findFirst("select o from Organization o where o.innerCode = ?",
                                                      new Object[] { innerCode });
    }
    
    public int countOperateLog(OperateLogQueryParameter queryParameter) {
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getOperateLogCount"));
        List<Object> params = new ArrayList<Object>();
        buildOperateLogQuery(hql, null, params, queryParameter);
        Number count = (Number) entityManager.findFirst(hql.toString(),
                                                        params.toArray());
        return count.intValue();
    }
    
    public List<Map> listOperateLog(OperateLogQueryParameter queryParameter) {
        StringBuilder countHql = new StringBuilder(QueryRepository.getInstance()
                                                                  .get("hql.getOperateLogCount"));
        StringBuilder hql = new StringBuilder(QueryRepository.getInstance()
                                                             .get("hql.getOperateLogList"));
        List<Object> params = new ArrayList<Object>();
        buildOperateLogQuery(hql, countHql, params, queryParameter);
        
        hql.append(" order by l.operateDate desc ");
        Page<Map> page = (Page<Map>) entityManager.findPage(countHql.toString(),
                                                            hql.toString(),
                                                            params.toArray(),
                                                            queryParameter.getStart(),
                                                            queryParameter.getLimit());
        return page.getData();
    }
    
    private void buildOperateLogQuery(StringBuilder sql,
                                      StringBuilder sql2,
                                      List<Object> params,
                                      OperateLogQueryParameter queryParameter) {
        if (sql == null || params == null || queryParameter == null) {
            return;
        }
        if (sql2 == null) {
            sql2 = new StringBuilder();
        }
        
        if (StringUtils.isNotBlank(queryParameter.getUserId())) {
            sql.append(" and l.user.id=? ");
            sql2.append(" and l.user.id=? ");
            params.add(queryParameter.getUserId());
        }
        
        if (StringUtils.isNotBlank(queryParameter.getOrganId())) {
            Organization org = entityManager.find(Organization.class,
                                                  queryParameter.getOrganId());
            if (org != null) {
                sql.append(" and o.innerCode like ?");
                sql2.append(" and o.innerCode like ?");
                params.add(org.getInnerCode() + "%");
            }
        }
        
        if (StringUtils.isNotBlank(queryParameter.getType())) {
            sql.append(" and l.type=? ");
            sql2.append(" and l.type=? ");
            params.add(queryParameter.getType());
        }
        
        if (StringUtils.isNotBlank(queryParameter.getClient())) {
            sql.append(" and l.client=? ");
            sql2.append(" and l.client=? ");
            params.add(queryParameter.getClient());
        }
        
        if (StringUtils.isNotBlank(queryParameter.getUri())) {
            sql.append(" and l.uri like ? ");
            sql2.append(" and l.uri like ? ");
            params.add("%" + queryParameter.getUri() + "%");
        }
        
        if (StringUtils.isNotBlank(queryParameter.getIp())) {
            sql.append(" and l.ip like ? ");
            sql2.append(" and l.ip like ? ");
            params.add("%" + queryParameter.getIp() + "%");
        }
        
        // 操作时间
        if (queryParameter.getStartDate() != null) {
            sql.append(" and l.operateDate>=? ");
            sql2.append(" and l.operateDate>=? ");
            params.add(queryParameter.getStartDate());
        }
        if (queryParameter.getEndDate() != null) {
            sql.append(" and l.operateDate<? ");
            sql2.append(" and l.operateDate<? ");
            params.add(DateUtils.addDay(queryParameter.getEndDate(), 1));
        }
    }
    
}
